#!/usr/bin/perl -w

use  feature  qw/say/;
use  strict;
use warnings;

use  Carp                           qw/confess/;
use  Cwd                            qw{abs_path     cwd            fast_abs_path};
use  Date::Calc                     qw(:all);
use  Excel::Writer::XLSX::Utility;
use  Excel::Writer::XLSX;
use  File::Basename;
use  File::Path                     qw(make_path);
use  FindBin                        qw($RealBin     $RealScript);
use  Getopt::Long;
#use  Env                            qw(WARD         WORK           USER);
#use  List::MoreUtils;

use lib "$RealBin/site_perl";
use lib "$RealBin";
#use Data::Printer {indent         => 1, multiline=>0};
use DDP;
use Carp::Always::Color;

use CON  qw/*CONm/;
use FlHandle;
#use ZipSlurp qw(read_text_zipped_okay);                                             
use zhdate; # chinese lunar calendar 


$|=1;

my $OutDir;

my $help;

my $rngstr =  &FlHandle::GetRndHex(12);
my $FileXlsxOut = q//;

my $syr = qx(date +"%Y");
chomp $syr;
my $szhDates      = undef;
my $FlgOfficeOpen = undef;
# my $strFont       = qq#jf-openhuninn-1.1#;    # 台北的粉圆字体
my $strFont       = qq#Courier New#;
my $moninit = 1;
my $monstep = 6;

GetOptions(
    "font=s"       => \$strFont,
    "moninit=i"    => \$moninit,
    "monstep=i"    => \$monstep,
    "open_office!" => \$FlgOfficeOpen,
    "out_dir=s"    => \$OutDir,
    "year=s"       => \$syr,
    "zhdate=s"     => \$szhDates,
);

if($help) { &print_usage(); }
if($OutDir  and (not -d $OutDir)) { 
    &make_path ($OutDir) or Carp::confess "cannot mkdir $OutDir";
}

my @Amn = ();
for (my $v428  = $moninit; $v428  < 13; $v428 +=$monstep) {
    push @Amn, $v428;
}


$OutDir = $OutDir // cwd();
$OutDir = abs_path($OutDir);

$szhDates = $szhDates // q(0101,0505,0815);

my $CnColor220 = '#ed4845';                # 淡菽红
my $CnColor334 = '#f25a47';                # 桂红
my $CnColor459 = '#f17666';                # 谷鞘红
my $CnColor147 = '#eeaa9c';                # 颊红


# pre-compiled regular expression patterns
#vim_folder_{{{

my $pt4 = qr/(?>\d{4})/; # non-backtracking 4-digit capturing
my $pt2 = qr/(?>\d{2})/; # non-backtracking 2-digit capturing

my $parenth_name = qr { \s* \(\s* \S+ \s* \)\s*}six;
my $parenth_only = qr { \s* \(\s*  \)\s*}six;
my $braces = qr{ ( \s* \{ (?: (?> [^\{\}]+ ) | (?-1) )* \}) }six;


my $parentheses = qr{ ( \s* \( (?: (?> [^\(\)]+ ) | (?-1) )* \)) }six;
#vim_folder_}}}

my $DateStr2 = q//;
my ( $yr6, $mn6, $dy6, $yr6b, $mn6b);
my @AMnt = ();
if ($syr) {
    my @Ayr = split ",", $syr;
    my @Ayr2 = grep { m#\d{4}# } @Ayr;
    my @Ayr2d = splice @Ayr, 0, 4;
    $DateStr2 = join "_", @Ayr2d;
    foreach my $y2 (@Ayr2) {
        foreach my $m3 ( @Amn ) {
            my $Dstr = sprintf "%04d-%02d-%02dT", $y2, $m3, 1;
            my ( $yr6c, $mn6c, $dy6c, $yr6b, $mn6b ) = &GetClosestSat($Dstr);
            my $Dstr6d = sprintf "%04d-%02d-%02dT", $yr6c, $mn6c, $dy6c;
            my $str172  = sprintf "%04dMn%02d", ${yr6b}, $mn6b;
            push @AMnt,
              {
                qq/DateT/  => $Dstr6d,
                qq/SheetN/ => $str172
              };
        }
    }
} else {

}

#else {
#    if ( $DateStr =~ m#\d{4}-\d\d-\d\dT$# ) {
#    } elsif ( $DateStr =~ m#\d{4}-\d\d# ) {
#        $DateStr .= qq(-01T);
#    } else {
#        Carp::confess "date string needs to be like 2019-05-26T";
#    }
#    ( $yr6, $mn6, $dy6, $yr6b, $mn6b ) = &GetClosestSat($DateStr);
#    $DateStr2 = sprintf "%04d-%02d", ${yr6b}, $mn6b;
#}


if(not $FileXlsxOut){ 
    $FileXlsxOut = "$OutDir/$DateStr2.s2.$rngstr.xlsx";
} elsif ($FileXlsxOut !~m#/#) {
    $FileXlsxOut = qq{$OutDir/$FileXlsxOut};
}

my $workbook = Excel::Writer::XLSX->new( $FileXlsxOut ) 
    or Carp::confess "-E- cannot write to xlsx file $FileXlsxOut";
printf "-I- writing to excel file  %8s\n", &CONm(  $FileXlsxOut, 8 );


# xlsx cell format
#vim_folder_{{{


my $DataF = $workbook->add_format(
    align      => 'center',
    border     => 0,
    font       => $strFont,
    num_format => 'dd',
    size       => 20,
    text_wrap  => 1,
);

$DataF->set_align('vcenter');

my $DF346 = $workbook->add_format();
$DF346->copy($DataF);
$DF346->set_font($strFont);
$DF346->set_font_outline(1);
$DF346->set_color($CnColor220); 
$DF346->set_bg_color($CnColor147); # 
$DF346->set_bold();
   
#    font       => qq#Courier New#,
my $DF2a = $workbook->add_format(
    bold       => 1,
    #border     => 3,
    border     => 0,
    font      => $strFont,
    num_format => 'mmm dd, yyyy',
    size       => 10,
    text_wrap  => 1,
);
$DF2a->set_align('vcenter');
$DF2a->set_align('right');
$DF2a->set_color('#2775b6'); # 景泰藍



my $DF2b = $workbook->add_format();
$DF2b->copy($DF2a);
$DF2b->set_num_format('mmm');
$DF2b->set_border(0);
#$DF2b->set_font('Calibri');
#$DF2b->set_font('Tahoma');
$DF2b->set_font_outline(1);
#$DF2b->set_font_shadow(1);
$DF2b->set_size(20);
#$DF2b->set_color('#223e36'); # 苍绿
$DF2b->set_color('#15231b'); # 云杉绿
$DF2b->set_align('center');

my $UnderlineFormat = $workbook->add_format();
$UnderlineFormat->copy($DataF);
$UnderlineFormat->set_bottom(6);
#$UnderlineFormat->set_bg_color(q(#B3C2F2));
#$UnderlineFormat->set_bg_color(q(#e0e0ff));
#$UnderlineFormat->set_bg_color(q(#c6e6e8));
#$UnderlineFormat->set_bg_color(q(#c4cbcf)); # 大理石灰
#$UnderlineFormat->set_bg_color(q(#c4d7d6)); # 穹灰
$UnderlineFormat->set_bg_color(q(#fffefa)); # 海参灰
#$UnderlineFormat->set_bottom_color(q(#9900cc));
$UnderlineFormat->set_bottom_color(q(#8b2671));
#$UnderlineFormat->set_bottom_color(q(#b598a1));
#$UnderlineFormat->set_color(q/#5F021F/);
$UnderlineFormat->set_color(q/#a61b29/);

my $FirstDayFormat = $workbook->add_format();
$FirstDayFormat->copy($DataF);
$FirstDayFormat->set_bg_color(q(#fc7930)); # 菠萝红
#$FirstDayFormat->set_bg_color(q(#ff9966));
$FirstDayFormat->set_bold();

my $Mon1Ft = $workbook->add_format();
$Mon1Ft->copy($DataF);
#$Mon1Ft->set_bg_color(q(#90f9b7));
$Mon1Ft->set_bg_color(q(#add5a2)); # 嘉陵水绿
$Mon1Ft->set_num_format(q(MMM)); 

my $Mon2Ft = $workbook->add_format();
$Mon2Ft->copy($DataF);
$Mon2Ft->set_bg_color(q(#b790f9));

my $Mon3Ft = $workbook->add_format();
$Mon3Ft->copy($DataF);
$Mon3Ft->set_bg_color(q(#90d1f9));

# Compact Cal title
my $SummDF2 = $workbook->add_format(
    align     => 'center',
    bold      => 1,
    border    => 0,
    bg_color  => q/#126e82/,              # 玉欽藍
    color     => q/#f4f4ff/,
    font      => $strFont,
    size      => 10,
    text_wrap => 1,
);
$SummDF2->set_align('vcenter');

#    bg_color     => q/#5757ff/,

my @AExcelColor = (8..63);
my $i_color_index = 20;
#vim_folder_}}}


my $WSheet1;
#print "### {"; print  __FILE__ ; print  "}-{" ; print __LINE__ ; print "}, "."\n";
#p @AMnt;
#exit;

if (@AMnt) {
    foreach my $mt7 (@AMnt) {
        $WSheet1 = $workbook->add_worksheet($mt7->{SheetN});
        &CreateWSheet( $WSheet1, $mt7 );
    }
} else {
    $WSheet1 = $workbook->add_worksheet($DateStr2);
    my $mn6a = sprintf "%02d", $mn6;
    my $dy6a = sprintf "%02d", $dy6;
    my $dstr6a = qq/$yr6-$mn6a-${dy6a}T/;
    &CreateWSheet( $WSheet1, {q(DateT) => $dstr6a, q(SheetN) => $DateStr2} );
}


$workbook->close();

printf "-I- Done writing excel file  %8s\n", &CONm(  $FileXlsxOut, 1 );

#say "\n\n\n";

if ($FlgOfficeOpen) {
    system qq(libreoffice --calc $FileXlsxOut&);

}




#------------------------------------------------------
# 
##------------------------------------------------------


sub print_usage
{#vim_folder_{{{
    my $st2  = &CONm(qq{$RealBin/$RealScript}, 8);



    my $usString = <<"EOUsage";
**USAGE**
 $st2  -next <dir>  -out <dir> -prev <dir1 dir2 dir3>


-Usage-  -out_dir        : Optional: output directory; default is current dir
-Usage-  -next           : rquired:  the newest (next) fct model area
-Usage-  -spread         : Optional: output spreadsheet file name; default is <workweek><random number>.xlsx
-Usage-  -prev           : Optional: previous fct areas or indicator-holding directories; can be multiple of them

EOUsage


    print "$usString\n\n";
}#vim_folder_}}}


sub CreateWSheet
{    #vim_folder_{{{
    my ($WSht1, $rH287) = @_;
    my $dstr6 = $rH287->{DateT};
    $WSht1->set_zoom(120);
    my $s500 = $rH287->{SheetN};
    my ($yr183, $mn183) = ( $s500=~m#(\d+)mn(\d+)#i);
    $WSht1->set_footer($s500);
    $WSht1->set_row( 0, 14 );
    $WSht1->set_row( 1, 15 );
    $WSht1->merge_range_type( 'date_time', 0, 2, 0, 8, $dstr6, $DF2a );
    $WSht1->merge_range( 1, 2, 1, 8, "Compact Calendar", $SummDF2 );

    my $SWSheetRow_i = 2;
    $WSht1->set_row( $SWSheetRow_i, 22 );

    
    $WSht1->write_date_time( $SWSheetRow_i, 8, $dstr6, $DataF );


    foreach my $i0 ( 2 .. 7 ) {
        $WSht1->write_formula( $SWSheetRow_i, $i0, qq#=INDIRECT("RC[+1]",0)-1#,
            $DataF );
    }
    $WSht1->write_formula( $SWSheetRow_i, 0, qq#{=IF(SUM(1*(DAY(C3:I3)=1))>0,I3, "")}#, $DF2b );
    $SWSheetRow_i += 1;
    while ( $SWSheetRow_i < 29 ) {
        $WSht1->set_row( $SWSheetRow_i, 23.6 );
        my $i2 = $SWSheetRow_i + 1;
        $WSht1->write_formula( $SWSheetRow_i, 0, qq#{=IF(SUM(1*(DAY(C$i2:I$i2)=1))>0,I$i2, "")}#, $DF2b );

        $WSht1->write_formula( $SWSheetRow_i, 2, qq#=INDIRECT("R[-1]C[+6]", 0)+1#, $DataF );
        foreach my $i0 ( 3 .. 8 ) {
            $WSht1->write_formula( $SWSheetRow_i, $i0, qq#=INDIRECT("RC[-1]",0)+1#, $DataF );
        }
        $SWSheetRow_i += 1;
    }
    # month-1, 4, 7, 10 (first month of each quarter) has background color 
    $WSht1->conditional_formatting( 'A3:A29', { type => 'formula', criteria => 'IF(MOD(MONTH(I3), 3)=1, 1, 0)', format => $Mon1Ft, } );
    
    # 农历
    my @AzhDates = split ",", $szhDates;
    foreach my $zhi (@AzhDates) {
        my ( $zmn, $zdy ) = ( $zhi =~ m#$pt2#ig );
        foreach my $yri3 ( $yr183 .. $yr183 + 1 ) {
            my ( $wyr389, $wmn389, $wdy389 ) = &zhdate::zhDate2wDate( $yri3, $zmn, $zdy );

            $WSht1->conditional_formatting(
                'C3:I29',
                {
                    type     => 'formula',
                    criteria => "IF(C3=DATE($wyr389, $wmn389, $wdy389), 1, 0)",
                    format   => $DF346,
                }
            );
        }
    }
    $WSht1->conditional_formatting(
        'C3:I29',
        {
            type     => 'formula',
            criteria => 'IF(DAY(C3)=1, 1, 0)',
            format   => $FirstDayFormat,
        }
    );
    $WSht1->conditional_formatting(
        'C3:I29',
        {
            type     => 'formula',
            criteria => 'DAY(C3)>DAY(C4)',
            format   => $UnderlineFormat,
        }
    );


    $WSht1->set_column( 0, 0, 7 );
    $WSht1->set_column( 1, 1, 0.4 );
    $WSht1->set_column( 2, 8, 5.5 );
    $SWSheetRow_i++;

}    #vim_folder_}}}




sub GetClosestSat
{    #_{{_
    my ($Tstr) = @_;
    my ( $yr, $mn, $dy ) = ( $Tstr =~ m#(\d+)-(\d+)-(\d+)T#six );


    my $dy1 = Date::Calc::Date_to_Days( $yr, $mn, $dy );
    my $dow = Day_of_Week( $yr, $mn, $dy );
    my ( $year6, $mon6, $day6 ) = Add_Delta_Days( 1, 1, 1, $dy1 - $dow - 2 );
    my ( $yr252, $mn252, $dy252 ) = Date::Calc::Add_Delta_YM( $year6, $mon6, $day6, 0, 1 );    # to obtain the next month which is the first whole month 

    $mn6 = sprintf "%02d", $mon6;
    return ( $year6, $mon6, $day6, $yr252, $mn252 );
}    #_}}_
